<html>
<head>
<title>Sales Detail Results</title>
<style type="text/css">
body {
	font-family:verdana,arial,sans-serif;
	font-size:10pt;
	margin:30px;
	background-color:#ffcc00;
	}
</style>
</head>
<body>
<?php
date_default_timezone_set('UTC');

$username="bsc353_4";
$password="sonbeans";
$database="bsc353_4";

$interval=$_POST['sales'];
$branchID=$_POST['branchid'];
$groupby=$_POST['group'];
$branch=$_POST['branchid'];

$intervalQuery;

if ($branch=='All Of MBS')
    $branch="";
else
    $branch="AND Sale.storeBranchID = $branch";


if ($groupby==""||$branch=="Select One")
{
    echo"Error. Please select an option for all fields.";
}

else
{

mysql_connect("clipper.encs.concordia.ca",$username,$password) or die ("Unable to connect to database");

@mysql_select_db($database) or die ("Unable to select database");

if ($branch != "")
{
    $query="SELECT name FROM StoreBranch WHERE branchID = " . $branchID;
    $result=mysql_query($query) or die ("failed to find record");
    $name = mysql_result($result,0,"name");
    echo "<p>$name - $branchID</p>";
}

for($i = 0; $i < 50; $i++)
{
    if ($interval=="Weekly")
    {
        $intervalQuery="DATE_SUB(CURDATE(), INTERVAL (($i+1)*7)-1 DAY) AND DATE_SUB(CURDATE(), INTERVAL ($i*7)-1 DAY)";
        $query="SELECT COUNT(*), DATE_SUB(CURDATE(), INTERVAL (($i+1)*7)-1 DAY) as Date FROM Sale";
        $result=mysql_query($query) or die ("failed to find record");
        $before = mysql_result($result,0,"Date");
        $query="SELECT COUNT(*), DATE_SUB(CURDATE(), INTERVAL ($i*7)-1 DAY) as Date FROM Sale";
        $result=mysql_query($query) or die ("failed to find record");
        $after = mysql_result($result,0,"Date");
    }
    else if ($interval=="Monthly")
    {
        $intervalQuery="DATE_SUB(DATE_SUB(CURDATE(), INTERVAL EXTRACT(DAY FROM CURDATE())-1 DAY), INTERVAL $i MONTH) AND DATE_SUB(DATE_SUB(CURDATE(), INTERVAL $i-1 MONTH), INTERVAL EXTRACT(DAY FROM CURDATE())-1 DAY)";
        $query="SELECT COUNT(*), DATE_SUB(DATE_SUB(CURDATE(), INTERVAL EXTRACT(DAY FROM CURDATE())-1 DAY), INTERVAL $i MONTH) as Date FROM Sale";
        $result=mysql_query($query) or die ("failed to find record");
        $before = mysql_result($result,0,"Date");
        $query="SELECT COUNT(*), DATE_SUB(DATE_SUB(CURDATE(), INTERVAL $i-1 MONTH), INTERVAL EXTRACT(DAY FROM CURDATE()) DAY) as Date FROM Sale";
        $result=mysql_query($query) or die ("failed to find record");
        $after = mysql_result($result,0,"Date");
    }
    else
    {
        $intervalQuery="DATE_SUB(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL EXTRACT(DAY FROM CURDATE())-1 DAY), INTERVAL EXTRACT(MONTH FROM CURDATE())-1 MONTH), INTERVAL $i YEAR) AND DATE_SUB(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL $i-1 YEAR), INTERVAL EXTRACT(MONTH FROM CURDATE())-1 MONTH), INTERVAL EXTRACT(DAY FROM CURDATE())-1 DAY)";
        $query="SELECT COUNT(*), DATE_SUB(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL EXTRACT(DAY FROM CURDATE())-1 DAY), INTERVAL EXTRACT(MONTH FROM CURDATE())-1 MONTH), INTERVAL $i YEAR) as Date FROM Sale";
        $result=mysql_query($query) or die ("failed to find record");
        $before = mysql_result($result,0,"Date");
        $query="SELECT COUNT(*), DATE_SUB(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL $i-1 YEAR), INTERVAL EXTRACT(MONTH FROM CURDATE())-1 MONTH), INTERVAL EXTRACT(DAY FROM CURDATE()) DAY) as Date FROM Sale";
        $result=mysql_query($query) or die ("failed to find record");
        $after = mysql_result($result,0,"Date");
    }

    $query="SELECT Sale.ISBN, Book.title, Book.subject, Book.publisher, Sale.saleValue, Sale.dateTime FROM Sale JOIN Book ON Sale.ISBN = Book.ISBN WHERE dateTime BETWEEN $intervalQuery $branch ORDER BY $groupby";

    $result=mysql_query($query) or die ("failed to find record");
    $num=mysql_numrows($result);

    if ($num != 0)
    {
        echo"<table border='1' cellspacing='2' cellpadding='2'>";
        echo"<th colspan='6'>$before to $after</th><tr>";
        echo"<td>ISBN</td>";
        echo"<td>Title</td>";
        echo"<td>Subject</td>";
        echo"<td>Publisher</td>";
        echo"<td>Sale Value</td>";
        echo"<td>Date Time</td>";
        echo"</tr>";
    }

    $a=0;
    while ($a < $num) 
    {
        $f1=mysql_result($result,$a,"Sale.ISBN");
        $f2=mysql_result($result,$a,"Book.title");
        $f3=mysql_result($result,$a,"Book.subject");
        $f4=mysql_result($result,$a,"Book.publisher");
        $f5=mysql_result($result,$a,"Sale.saleValue");
        $f6=mysql_result($result,$a,"Sale.dateTime");

        echo"<tr>";
        echo"<td>$f1</td>";
        echo"<td>$f2</td>";
        echo"<td>$f3</td>";
        echo"<td>$f4</td>";
        echo"<td>$f5</td>";
        echo"<td>$f6</td>";
        echo"</tr>";

        $a++;
    }

    if ($num != 0) echo"</table><br />";

}

mysql_close();
}


?>

</body>
</html>